﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.project.dao.impl;

import java.text.SimpleDateFormat;
import java.util.*;

import com.hh.bss.auth.util.AuthSqlUtil;
import com.hh.bss.base.*;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.cache.SystemUserCache;
import com.hh.bss.common.page.*;
import com.hh.bss.project.dao.*;
import com.hh.bss.project.entity.*;
import com.hh.bss.project.vo.query.*;
import com.hh.bss.util.*;



import static com.hh.bss.util.ObjectUtils.*;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

@Repository
public class ContractJobDaoImpl extends BaseHibernateDao<ContractJob, java.lang.Integer> implements ContractJobDao<ContractJob, java.lang.Integer> {

	public Class getEntityClass() {
		return ContractJob.class;
	}

	public List findProjectJob(ContractJobQuery query) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" SELECT a.id, a.name,d.abbr_name,a.create_dt createDt,concat(a.name,'--->',d.abbr_name) as names  FROM contract_job a INNER JOIN contract_job_search_project b ON a.id = b.job_id INNER JOIN customer c ON a.cust_id = c.id INNER JOIN company d ON c.company_id = d.id WHERE b.project_id = ? ");
		values.add(query.getProjectId());
		sql.append(" order by createDt desc ");

		return super.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());

	}

	public Page findProjectJobPage(ContractJobQuery query) {
		StringBuffer sql = new StringBuffer();
		// 单独给CC项目的相关职位使用的
		List values = new ArrayList();
		// 客户公司，职位名，编号，职位合作性质，客户职位联系人，职位负责人，合作成员，职位进展，推荐数，客户面试数，背景调查，录用，上班
		/*
		 * sql.append(" select * from ( "); sql.append(
		 * " select a.pjid id,k.contractId,a.cust_id custId,b.companyName,a.id jobId,a.status,a.industry,a.creator,a.function,a.level,a.name,a.job_no jobNo,a.job_nature jobNature,a0.cv_name hrContacter,h.jobManager,i.jobCooper,j.jobAssist,if(ifnull(f.offerCount,0)>0,'成功',if(ifnull(d.interviewCount,0)>0,'面试中',if(ifnull(c.recomCount,0)>0,'推荐中','寻访中'))) jobStatus, '' jobCoopMember ,ifnull(c.recomCount,0) recomCount,ifnull(d.interviewCount,0) interviewCount,ifnull(e.backgroundCount,0) backgroundCount,ifnull(f.offerCount,0) offerCount,ifnull(g.workCount,0) workCount  from (select a0.*,a1.id pjid from  contract_job a0,contract_job_search_project a1 where a1.project_id=? and a0.id=a1.job_id  )a "
		 * ); sql.append(" left join  cv a0 on a.hr_contacter=a0.id ");
		 * sql.append(
		 * " left join ( select b1.id companyId,b2.id,b1.name companyName,b1.full_name companyFullName from  company b1,customer b2 where b2.company_id=b1.id ) b on a.cust_id=b.id "
		 * ); sql.append(
		 * " left join  (select job_id,count(*) recomCount from contract_job_cv_recom group by job_id) c on a.id=c.job_id "
		 * ); sql.append(
		 * " left join  (select t2.job_id,count(distinct t1.recom_id) interviewCount from contract_job_cv_interview t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) d on a.id=d.job_id "
		 * ); sql.append(
		 * " left join  (select t2.job_id,count(distinct t1.recom_id) backgroundCount from contract_job_cv_background t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) e on a.id=e.job_id "
		 * ); sql.append(
		 * " left join  (select t2.job_id,count(distinct t1.recom_id) offerCount from contract_job_cv_offer t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) f on a.id=f.job_id "
		 * ); sql.append(
		 * " left join  (select t2.job_id,count(distinct t1.recom_id) workCount from contract_job_cv_crm t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) g on a.id=g.job_id "
		 * ); sql.append(
		 * " left join  ( select a.job_id,group_concat((b.name) separator ',') as jobManager from contract_job_partner a,system_user b where a.account=b.account and a.role='"
		 * ).append(ConstantDic.JOB_PARTNER_TYPE_MC).append(
		 * "' group by a.job_id) h on h.job_id=a.id"); sql.append(
		 * " left join  ( select a.job_id,group_concat((b.name) separator ',') as jobCooper from contract_job_partner a,system_user b where a.account=b.account and a.role='"
		 * ).append(ConstantDic.JOB_PARTNER_TYPE_PC).append(
		 * "' group by a.job_id) i on i.job_id=a.id"); sql.append(
		 * " left join  ( select a.job_id,group_concat((b.name) separator ',') as jobAssist from contract_job_partner a,system_user b where a.account=b.account and a.role='"
		 * ).append(ConstantDic.JOB_PARTNER_TYPE_BA).append(
		 * "' group by a.job_id) j on j.job_id=a.id"); sql.append(
		 * " left join (select a.id,max(b.id) contractId from customer a,customer_contract b where a.id=b.cust_id group by a.id) k on a.cust_id=k.id "
		 * ); sql.append(" ) jobinfo ");
		 */
		sql.append(" select * from ( ");
		sql.append(" select a.pjid id,k.contractId,l.name projectName,a.cust_id custId,b.companyName,a.id jobId,a.status,a.industry,a.creator,a.function,a.level,a.name,a.job_no jobNo,a.job_nature jobNature,a0.cv_name hrContacter,a.job_manager jobManager,a.job_cooper jobCooper,a.job_assist jobAssist,if(ifnull(f.offerCount,0)>0,if(n.succStatus=1,if(n.final_ar=n.arAmount,'成功','已经上班'),'offer'),if(ifnull(d.interviewCount,0)>0,'面试中',if(ifnull(c.recomCount,0)>0,'推荐中','寻访中'))) jobStatus, '' jobCoopMember ,ifnull(c.recomCount,0) recomCount,ifnull(d.interviewCount,0) interviewCount,ifnull(e.backgroundCount,0) backgroundCount,ifnull(f.offerCount,0) offerCount,ifnull(f.workCount,0) workCount  from (select a0.*,a1.id pjid,a1.project_id from  contract_job a0,contract_job_search_project a1 where a1.project_id=? and a0.id=a1.job_id  )a ");
		sql.append(" left join  cv a0 on a.hr_contacter=a0.id ");
		sql.append(" left join ( select b1.id companyId,b2.id,b1.name companyName,b1.full_name companyFullName from  company b1,customer b2 where b2.company_id=b1.id ) b on a.cust_id=b.id ");
		sql.append(" left join  (select job_id,count(*) recomCount from contract_job_cv_recom group by job_id) c on a.id=c.job_id ");
		sql.append(" left join  (select t2.job_id,count(distinct t1.recom_id) interviewCount from contract_job_cv_interview t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) d on a.id=d.job_id ");
		sql.append(" left join  (select t2.job_id,count(distinct t1.recom_id) backgroundCount from contract_job_cv_background t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) e on a.id=e.job_id ");
		sql.append(" left join  (select t2.job_id,count(distinct t1.recom_id) offerCount,sum(if(t1.checkin_d is null,0,1)) workCount from contract_job_cv_offer t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) f on a.id=f.job_id ");
		// sql.append(" left join  (select t2.job_id,count(distinct t1.recom_id) workCount from contract_job_cv_crm t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) g on a.id=g.job_id ");
		// sql.append(" left join  ( select a.job_id,group_concat((b.name) separator ',') as jobManager from contract_job_partner a,system_user b where a.account=b.account and a.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' group by a.job_id) h on h.job_id=a.id");
		// sql.append(" left join  ( select a.job_id,group_concat((b.name) separator ',') as jobCooper from contract_job_partner a,system_user b where a.account=b.account and a.role='").append(ConstantDic.JOB_PARTNER_TYPE_PC).append("' group by a.job_id) i on i.job_id=a.id");
		// sql.append(" left join  ( select a.job_id,group_concat((b.name) separator ',') as jobAssist from contract_job_partner a,system_user b where a.account=b.account and a.role='").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("' group by a.job_id) j on j.job_id=a.id");
		// sql.append(" left join (select a.id,max(b.id) contractId from customer a,customer_contract b where a.id=b.cust_id group by a.id) k on a.cust_id=k.id ");
		sql.append(" left join (select a.id,max(ifnull(b.id,ifnull(c.contract_id,null))) contractId from customer a left join customer_contract b on a.id=b.cust_id left join customer_contract_service_company c on a.id=c.cust_id  group by a.id) k on a.cust_id=k.id ");
		sql.append(" left join  search_project l  on a.project_id=l.id ");
		sql.append(" left join  (select t2.job_id,min(if(ifnull(t1.status,0)=1,1,0)) succStatus,sum(t3.final_ar) final_ar,sum(t4.arAmount) arAmount from contract_job_cv_offer t1 inner join contract_job_cv_recom t2 on t1.recom_id=t2.id left join account_pn t3 on t1.id=t3.offer_id left join (select t3.offer_id,sum(t4.amount) arAmount from account_pn t3 left join account_ar t4 on t3.nbr=t4.pn_nbr group by t3.offer_id) t4 on t1.id=t4.offer_id group by t2.job_id) n on a.id=n.job_id ");
		

		sql.append(" ) jobinfo ");

		sql.append(" where 1=1 ");

		values.add(query.getProjectId());

		if (isNotEmpty(query.getCustId())) {
			sql.append(" and jobinfo.custId=? ");
			values.add(query.getCustId());
		}

		if (isNotEmpty(query.getName())) {
			sql.append(" and jobinfo.name like ? ");
			values.add("%" + query.getName() + "%");
		}
		if (isNotEmpty(query.getCompanyName())) {
			sql.append(" and jobinfo.companyName like ? ");
			values.add("%" + query.getCompanyName() + "%");
		}
		if (isNotEmpty(query.getCustName())) {
			sql.append(" and jobinfo.companyName like ? ");
			values.add("%" + query.getCustName() + "%");
		}
		if (isNotEmpty(query.getIndustry())) {
			sql.append(" and jobinfo.industry = ? ");
			values.add(query.getIndustry());
		}
		if (isNotEmpty(query.getFunction())) {
			sql.append(" and jobinfo.function = ? ");
			values.add(query.getFunction());
		}
		if (isNotEmpty(query.getLevel())) {
			sql.append(" and jobinfo.level = ? ");
			values.add(query.getLevel());
		}
		if (isNotEmpty(query.getJobNo())) {
			sql.append(" and jobinfo.jobNo like ? ");
			values.add("%" + query.getJobNo() + "%");
		}
		// 增加权限控制

		if (query.getOnlineUser() != null) {
			String authsql = AuthSqlUtil.authJobSQL(query.getOnlineUser().getUsername(), "jobinfo.jobId", "jobinfo.creator");
			if (!StringHelper.isEmpty(authsql))
				sql.append(authsql);
		}
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	public Page findJobPage(ContractJobQuery query) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		// 客户公司，职位名，编号，职位合作性质，客户职位联系人，职位负责人，合作成员，职位进展，推荐数，客户面试数，背景调查，录用，上班
		/*
		 * sql.append(" select * from ( "); sql.append(
		 * " select a.id,a.cust_id custId,k.contractId,b.companyName,a.id jobId,a.status,a.industry,a.creator,a.create_dt createDt ,a.modifier ,a.modified_dt modifiedDt,a.function,a.level,a.name,a.job_no jobNo,a.job_nature jobNature,a0.cv_name hrContacter,h.jobManager,i.jobCooper,j.jobAssist,if(ifnull(f.offerCount,0)>0,'成功',if(ifnull(d.interviewCount,0)>0,'面试中',if(ifnull(c.recomCount,0)>0,'推荐中','寻访中'))) jobStatus, '' jobCoopMember ,ifnull(c.recomCount,0) recomCount,ifnull(d.interviewCount,0) interviewCount,ifnull(e.backgroundCount,0) backgroundCount,ifnull(f.offerCount,0) offerCount,ifnull(g.workCount,0) workCount  from  contract_job a "
		 * ); sql.append(" left join  cv a0 on a.hr_contacter=a0.id ");
		 * sql.append(
		 * " left join ( select b1.id companyId,b2.id,b1.name companyName,b1.full_name companyFullName from  company b1,customer b2 where b2.company_id=b1.id ) b on a.cust_id=b.id "
		 * ); sql.append(
		 * " left join  (select job_id,count(*) recomCount from contract_job_cv_recom group by job_id) c on a.id=c.job_id "
		 * ); sql.append(
		 * " left join  (select t2.job_id,count(distinct t1.recom_id) interviewCount from contract_job_cv_interview t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) d on a.id=d.job_id "
		 * ); sql.append(
		 * " left join  (select t2.job_id,count(distinct t1.recom_id) backgroundCount from contract_job_cv_background t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) e on a.id=e.job_id "
		 * ); sql.append(
		 * " left join  (select t2.job_id,count(distinct t1.recom_id) offerCount from contract_job_cv_offer t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) f on a.id=f.job_id "
		 * ); sql.append(
		 * " left join  (select t2.job_id,count(distinct t1.recom_id) workCount from contract_job_cv_crm t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) g on a.id=g.job_id "
		 * ); sql.append(
		 * " left join  ( select a.job_id,group_concat((b.name) separator ',') as jobManager from contract_job_partner a,system_user b where a.account=b.account and a.role='"
		 * ).append(ConstantDic.JOB_PARTNER_TYPE_MC).append(
		 * "' group by a.job_id) h on h.job_id=a.id"); sql.append(
		 * " left join  ( select a.job_id,group_concat((b.name) separator ',') as jobCooper from contract_job_partner a,system_user b where a.account=b.account and a.role='"
		 * ).append(ConstantDic.JOB_PARTNER_TYPE_PC).append(
		 * "' group by a.job_id) i on i.job_id=a.id"); sql.append(
		 * " left join  ( select a.job_id,group_concat((b.name) separator ',') as jobAssist from contract_job_partner a,system_user b where a.account=b.account and a.role='"
		 * ).append(ConstantDic.JOB_PARTNER_TYPE_BA).append(
		 * "' group by a.job_id) j on j.job_id=a.id"); sql.append(
		 * " left join (select a.id,max(b.id) contractId from customer a,customer_contract b where a.id=b.cust_id group by a.id) k on a.cust_id=k.id "
		 * ); sql.append(" ) jobinfo ");
		 */

		sql.append(" select * from ( ");
		sql.append(" select a.id,a.cust_id custId,k.contractId,a.id jobId,a.is_pub isPub,a.status,a.industry,a.creator,a.create_dt createDt ,a.modifier ,a.modified_dt modifiedDt,a.function,a.level,a.name,a.job_no jobNo,a.job_nature jobNature,a0.cv_name hrContacter,a.job_manager jobManager,a.job_cooper jobCooper,a.job_assist jobAssist,if(ifnull(f.offerCount,0)>0,if(n.succStatus=1,if(n.final_ar=n.arAmount,'成功','已经上班'),'offer'),if(ifnull(d.interviewCount,0)>0,'面试中',if(ifnull(c.recomCount,0)>0,'推荐中','寻访中'))) jobStatus, '' jobCoopMember ,ifnull(b.jdCount,0) jdCount,ifnull(c.recomCount,0) recomCount,ifnull(d.interviewCount,0) interviewCount,ifnull(e.backgroundCount,0) backgroundCount,ifnull(f.offerCount,0) offerCount,ifnull(f.workCount,0) workCount,datediff(curdate(),a.create_dt) days  from  contract_job a ");
		sql.append(" left join  cv a0 on a.hr_contacter=a0.id ");
		// sql.append(" left join ( select b1.id companyId,b2.id,b1.name companyName,b1.full_name companyFullName,b1.address from  company b1,customer b2 where b2.company_id=b1.id ) b on a.cust_id=b.id ");
		sql.append(" left join  (select job_id,count(distinct cv_id) jdCount from contract_job_cv_jd where type=" + ContractJobCvJd.TYPE_JD + " group by job_id) b on a.id=b.job_id ");
		sql.append(" left join  (select job_id,count(*) recomCount from contract_job_cv_recom where is_email = 1  group by job_id) c on a.id=c.job_id ");
		sql.append(" left join  (select t2.job_id,count(distinct t1.recom_id) interviewCount from contract_job_cv_interview t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) d on a.id=d.job_id ");
		sql.append(" left join  (select t2.job_id,count(distinct t1.recom_id) backgroundCount from contract_job_cv_background t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) e on a.id=e.job_id ");
		sql.append(" left join  (select t2.job_id,count(distinct t1.recom_id) offerCount,sum(if(t1.checkin_d is null,0,1)) workCount from contract_job_cv_offer t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) f on a.id=f.job_id ");
		// sql.append(" left join  (select t2.job_id,count(distinct t1.recom_id) workCount from contract_job_cv_crm t1,contract_job_cv_recom t2 where t1.recom_id=t2.id  group by t2.job_id) g on a.id=g.job_id ");
		// sql.append(" left join  (  select a.job_id,if(a.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("',group_concat((b.name) separator ','),'') as jobManager,if(a.role='").append(ConstantDic.JOB_PARTNER_TYPE_PC).append("',group_concat((b.name) separator ','),'') as jobCooper,if(a.role='").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("',group_concat((b.name) separator ','),'') as jobAssist from contract_job_partner a,system_user b where a.account=b.account  group by a.job_id) h on h.job_id=a.id");
		// sql.append(" left join  ( select a.job_id,group_concat((b.name) separator ',') as jobCooper from contract_job_partner a,system_user b where a.account=b.account and a.role='").append(ConstantDic.JOB_PARTNER_TYPE_PC).append("' group by a.job_id) i on i.job_id=a.id");
		// sql.append(" left join  ( select a.job_id,group_concat((b.name) separator ',') as jobAssist from contract_job_partner a,system_user b where a.account=b.account and a.role='").append(ConstantDic.JOB_PARTNER_TYPE_BA).append("' group by a.job_id) j on j.job_id=a.id");
		// sql.append(" left join (select a.id,max(b.id) contractId from customer a,customer_contract b where a.id=b.cust_id group by a.id) k on a.cust_id=k.id ");
		sql.append(" left join (select a.id,max(ifnull(b.id,ifnull(c.contract_id,null))) contractId from customer a left join customer_contract b on a.id=b.cust_id left join customer_contract_service_company c on a.id=c.cust_id  group by a.id) k on a.cust_id=k.id ");
		sql.append(" left join  (select t2.job_id,min(if(ifnull(t1.status,0)=1,1,0)) succStatus,sum(t3.final_ar) final_ar,sum(t4.arAmount) arAmount from contract_job_cv_offer t1 inner join contract_job_cv_recom t2 on t1.recom_id=t2.id left join account_pn t3 on t1.id=t3.offer_id left join (select t3.offer_id,sum(t4.amount) arAmount from account_pn t3 left join account_ar t4 on t3.nbr=t4.pn_nbr group by t3.offer_id) t4 on t1.id=t4.offer_id group by t2.job_id) n on a.id=n.job_id ");
		// sql.append(" left join system_user x2 on a.creator = x2.account ");
		sql.append(" ) jobinfo ");

		sql.append(" where 1=1 ");

		if (query.isDefault()) {
			// sql.append(" and exists (select 1 from contract_job_partner b where jobinfo.id=b.job_id and b.account=?) ");
			// values.add(query.getOnlineUser().getUsername());
		}

		if (isNotEmpty(query.getProjectId())) {
			sql.append(" and exists( select 1 from contract_job_search_project t1 where t1.project_id=? and t1.job_id= jobinfo.jobId) ");
			values.add(query.getProjectId());
		}

		if (isNotEmpty(query.getCustId())) {
			sql.append(" and jobinfo.custId=? ");
			values.add(query.getCustId());
		}
		if (isNotEmpty(query.getIsPub())) {
			sql.append(" and ifnull(jobinfo.isPub,0)=? ");
			values.add(query.getIsPub());
		}

		if (isNotEmpty(query.getName())) {
			sql.append(" and jobinfo.name like ? ");
			values.add("%" + query.getName() + "%");
		}
		if (isNotEmpty(query.getCompanyName())) {
			// sql.append(" and jobinfo.companyName like ? ");
			// values.add("%"+query.getCompanyName()+"%");
		}
		if (isNotEmpty(query.getCustName())) {
			// sql.append(" and jobinfo.companyName like ? ");
			sql.append(" and exists (select 1 from  company b1,customer b2 where b2.company_id=b1.id and jobinfo.custId=b2.id and (b1.abbr_name like ? or b1.name like ? or b1.full_name like ?  ) ) ");
			values.add("%" + query.getCustName() + "%");
			values.add("%" + query.getCustName() + "%");
			values.add("%" + query.getCustName() + "%");
		}
		// 一级行业
		if (isNotEmpty(query.getParentIndustry())) {
			sql.append(" and  exists( select 1 from system_dic a where jobinfo.industry = a.id  and a.category='INDUSTRY' and a.parent_id= ? )");
			values.add(query.getParentIndustry());
		}

		if (isNotEmpty(query.getIndustry())) {
			sql.append(" and jobinfo.industry = ? ");
			values.add(query.getIndustry());
		}
		// 一级职能
		if (isNotEmpty(query.getParentFunction())) {
			sql.append(" and  exists(select 1 from system_dic a where jobinfo.function = a.id  and a.category='JOB' and a.parent_id= ? )");
			values.add(query.getParentFunction());
		}
		if (isNotEmpty(query.getFunction())) {
			sql.append(" and jobinfo.function = ? ");
			values.add(query.getFunction());
		}
		if (isNotEmpty(query.getLevel())) {
			sql.append(" and jobinfo.level = ? ");
			values.add(query.getLevel());
		}
		if (isNotEmpty(query.getJobNo())) {
			sql.append(" and jobinfo.jobNo like ? ");
			values.add("%" + query.getJobNo() + "%");
		}
		// 2012年7月30日 17:42:58 Alvin 增加查询条件 职位负责人、合作、职位状态
		if (isNotEmpty(query.getJobManager())) {
			// sql.append(" and jobinfo.jobManager = ? ");
			sql.append(" and exists (select 1 from contract_job_partner b where jobinfo.id=b.job_id and b.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' and b.account=?) ");
			values.add(query.getJobManager());
		}
		if (isNotEmpty(query.getJobCooper())) {
			// sql.append(" and jobinfo.jobCooper = ? ");
			sql.append(" and exists (select 1 from contract_job_partner b where jobinfo.id=b.job_id and b.role='").append(ConstantDic.JOB_PARTNER_TYPE_PC).append("' and b.account=?) ");
			values.add(query.getJobCooper());
		}
		if (isNotEmpty(query.getStatus())) {
			sql.append(" and jobinfo.status = ? ");
			values.add(query.getStatus());
		}
		if (isNotEmpty(query.getCreateDt())) {
			sql.append(" and jobinfo.createDt >= ? ");
			values.add(query.getCreateDt());
		}
		if(isNotEmpty(query.getJobPartner())){
			sql.append(" and exists (select 1 from contract_job_partner b where jobinfo.id=b.job_id and find_in_set(b.role,'MC,PC') and  b.account=? ) ");
			values.add(query.getJobPartner());
		}
		// 增加权限控制

		if (query.getOnlineUser() != null) {
			String authsql = AuthSqlUtil.authJobSQL(query.getOnlineUser().getUsername(), "jobinfo.jobId", "jobinfo.creator");
			if (!StringHelper.isEmpty(authsql))
				sql.append(authsql);
		}
		if (isNotEmpty(query.getSortColumns())) { // 开启远程排序
			sql.append(" order by jobinfo.").append(query.getSortColumns());
		} else {
			sql.append(" order by ifnull (if(jobinfo.status !=2,jobinfo.modifiedDt,null),jobinfo.createDt)  desc"); // jobinfo.modifiedDt
		}
		log.info("职位查询:"+sql.toString());
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	public Page findPage(ContractJobQuery query) {
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		Class[] os = { ContractJobQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		// 生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
		StringBuilder sql2 = new StringBuilder("select t from ContractJob t where 1=1 ");
		if (isNotEmpty(query.getId())) {
			sql2.append(" and  t.id = :id ");
		}
		if (isNotEmpty(query.getCustId())) {
			sql2.append(" and  t.custId = :custId ");
		}
		if (isNotEmpty(query.getContractId())) {
			sql2.append(" and  t.contractId = :contractId ");
		}
		if (isNotEmpty(query.getProjectId())) {
			sql2.append(" and  t.id in  (select a.jobId  from ContractJobSearchProject a where a.projectId = :projectId )");
		}

		if (isNotEmpty(query.getName())) {
			sql2.append(" and  t.name like :name ");
			query.setName("%" + query.getName() + "%");

		}
		if (isNotEmpty(query.getDispLevel())) {
			sql2.append(" and  t.dispLevel = :dispLevel ");
		}
		if (isNotEmpty(query.getTrialMonths())) {
			sql2.append(" and  t.trialMonths = :trialMonths ");
		}
		if (isNotEmpty(query.getIndustry())) {
			sql2.append(" and  t.industry = :industry ");
		}
		if (isNotEmpty(query.getFunction())) {
			sql2.append(" and  t.function = :function ");
		}
		if (isNotEmpty(query.getLevel())) {
			sql2.append(" and  t.level = :level ");
		}
		if (isNotEmpty(query.getLanguages())) {
			sql2.append(" and  t.languages like :languages ");
			query.setLanguages("%" + query.getLanguages() + "%");

		}
		if (isNotEmpty(query.getSex())) {
			sql2.append(" and  t.sex like :sex ");
			query.setSex("%" + query.getSex() + "%");

		}
		if (isNotEmpty(query.getMinAge())) {
			sql2.append(" and  t.minAge = :minAge ");
		}
		if (isNotEmpty(query.getMaxAge())) {
			sql2.append(" and  t.maxAge = :maxAge ");
		}
		if (isNotEmpty(query.getOtherRequire())) {
			sql2.append(" and  t.otherRequire like :otherRequire ");
			query.setOtherRequire("%" + query.getOtherRequire() + "%");

		}
		if (isNotEmpty(query.getWorkLocation())) {
			sql2.append(" and  t.workLocation = :workLocation ");
		}
		if (isNotEmpty(query.getHireNum())) {
			sql2.append(" and  t.hireNum = :hireNum ");
		}
		if (isNotEmpty(query.getOpenDBegin())) {
			sql2.append(" and  t.openD >= :openDBegin ");
		}
		if (isNotEmpty(query.getOpenDEnd())) {
			sql2.append(" and  t.openD <= :openDEnd ");
		}
		if (isNotEmpty(query.getOpenResult())) {
			sql2.append(" and  t.openResult like :openResult ");
			query.setOpenResult("%" + query.getOpenResult() + "%");

		}
		if (isNotEmpty(query.getSalary())) {
			sql2.append(" and  t.salary like :salary ");
			query.setSalary("%" + query.getSalary() + "%");

		}
		if (isNotEmpty(query.getTitleId())) {
			sql2.append(" and  t.titleId = :titleId ");
		}
		if (isNotEmpty(query.getHrContacter())) {
			sql2.append(" and  t.hrContacter = :hrContacter ");
		}
		if (isNotEmpty(query.getArContacter())) {
			sql2.append(" and  t.arContacter = :arContacter ");
		}
		if (isNotEmpty(query.getInterviewerFlow())) {
			sql2.append(" and  t.interviewerFlow like :interviewerFlow ");
			query.setInterviewerFlow("%" + query.getInterviewerFlow() + "%");

		}
		if (isNotEmpty(query.getComment())) {
			sql2.append(" and  t.comment like :comment ");
			query.setComment("%" + query.getComment() + "%");

		}
		if (isNotEmpty(query.getCreator())) {
			sql2.append(" and  t.creator like :creator ");
			query.setCreator("%" + query.getCreator() + "%");

		}
		if (isNotEmpty(query.getCreateDtBegin())) {
			sql2.append(" and  t.createDt >= :createDtBegin ");
		}
		if (isNotEmpty(query.getCreateDtEnd())) {
			sql2.append(" and  t.createDt <= :createDtEnd ");
		}
		if (isNotEmpty(query.getModifier())) {
			sql2.append(" and  t.modifier like :modifier ");
			query.setModifier("%" + query.getModifier() + "%");

		}
		if (isNotEmpty(query.getModifiedDtBegin())) {
			sql2.append(" and  t.modifiedDt >= :modifiedDtBegin ");
		}
		if (isNotEmpty(query.getModifiedDtEnd())) {
			sql2.append(" and  t.modifiedDt <= :modifiedDtEnd ");
		}
		if (isNotEmpty(query.getSortColumns())) {
			sql2.append(" order by :sortColumns ");
		}

		return pageQuery(sql2.toString(), query);
	}

	/**
	 * 根据用户名生成职位编号
	 * 
	 * @param userName
	 *            用户名
	 * @return 职位编号
	 * @update 2).职位管理 职位编号的改变 职位编号=职位公司ID+年月4位+4位序号 每年重置.
	 */
	public String getNextPositionNo(ContractJob contractJob) {
		SimpleDateFormat format = new SimpleDateFormat("yyMM");
		String companyIdTemp = "";
		if (contractJob.getCompanyId() == null || contractJob.getCompanyId() > 0) {
			String query = "select a.company_id  from customer a where a.id = ? ";
			companyIdTemp = (String) super.getJdbcDao().getJdbcTemplate().queryForObject(query, new Object[] { contractJob.getCustId() }, String.class);
		} else {
			companyIdTemp = contractJob.getCompanyId().toString();
		}
		String companyId = StringUtils.leftPad(companyIdTemp + "", 6, "0");
		// String query =
		// "select branch_company_id from system_user where account ='"+contractJob.getCreator()+"'";
		// //根据用户名来获取用户所在的公司
		// String branchCompanyId = (String)
		// super.getJdbcDao().getJdbcTemplate().queryForObject(query,
		// String.class);
		String date = format.format(new Date());
		// String sqls =
		// "select a.job_no from contract_job a ,system_user b  where a.creator=b.account and a.create_dt > SUBSTR(CURDATE(),1,4) and  b.branch_company_id = ? order by a.id desc ";
		String sqls = "select a.job_no from contract_job a   where   a.cust_id = ? and a.create_dt > SUBSTR(CURDATE(),1,4) order by a.id desc ";
		List jobNoList = super.getJdbcDao().getJdbcTemplate().queryForList(sqls, String.class, contractJob.getCustId());// queryForList(sqls,String.class);//queryForList(sqls,
																														// String.class);
		String jobNo = null;
		if (jobNoList != null && jobNoList.size() > 0) {
			jobNo = (String) jobNoList.get(0);
		}
		if (StringHelper.isEmpty(jobNo) || jobNo.length() != 13) {
			return new StringBuffer().append(companyId).append(date).append("001").toString();
		}
		String jobNum = jobNo.substring(10); // 120120001
		Integer num = Integer.parseInt(jobNum) + 1;
		String nums = StringUtils.leftPad(num.toString(), 3, "0");
		return companyId + date + nums;
	}

	public Page queryJobPage(ContractJobQuery query) {
		// TODO Auto-generated method stub

		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		sql.append(" select a.id,a.cust_id custId,a.id jobId,a.status,a.industry,a.creator,a.create_dt createDt ,a.modifier ,a.modified_dt modifiedDt,a.function,a.level,a.name,a.job_no jobNo,a.job_nature jobNature,a0.cv_name hrContacter,a.job_manager jobManager,a.job_cooper jobCooper,a.job_assist jobAssist,a.work_location workLocation  from  contract_job a ");

		sql.append(" ) jobinfo ");

		sql.append(" where 1=1 ");

		if (isNotEmpty(query.getProjectId())) {
			sql.append(" and exists( select 1 from contract_job_search_project t1 where t1.project_id=? and t1.job_id= jobinfo.jobId) ");
			values.add(query.getProjectId());
		}

		if (isNotEmpty(query.getCustId())) {
			sql.append(" and jobinfo.custId=? ");
			values.add(query.getCustId());
		}

		if (isNotEmpty(query.getName())) {
			sql.append(" and jobinfo.name like ? ");
			values.add("%" + query.getName() + "%");
		}

		if (isNotEmpty(query.getIndustry())) {
			sql.append(" and jobinfo.industry = ? ");
			values.add(query.getIndustry());
		}

		if (isNotEmpty(query.getWorkLocation())) {
			sql.append(" and jobinfo.workLocation = ? ");
			values.add(query.getWorkLocation());
		}

		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	public Page findGWPage(ContractJobQuery query) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		// 职位ID:
		// 职位名:
		// 工作地点:
		// 职位描述(是不是现在VITAE的职责?)
		// 发布日期:是不是现在vitae的开放日期?

		sql.append(" select * from ( ");
		sql.append(" select a.id,a.id jobId,a.cust_id custId,a.status,a.industry,f.code_name_cn industryName,c.code_name_cn workLocationName,a.work_location ,a.creator,a.job_manager jobManager,a.create_dt createDt,DATE_FORMAT(DATE_SUB(now(),INTERVAL a.id%5 day),'%Y-%m-%d') openD,a.name,a.duty,a.requirement,a.hire_num hireNum,e.abbr_name companyName,e.job_profile jobProfile,e.property ,g.code_name_cn propertyName  from  contract_job a ");
		sql.append(" left join   system_dic  c on c.id=a.work_location ");
		// sql.append(" left join   system_dic  f on f.id=a.industry ");
		sql.append(" left join customer d on a.cust_id = d.id  ");
		sql.append(" left join company e on d.company_id = e.id  ");
		sql.append(" left join   system_dic  f on f.id=a.industry ");
		sql.append(" left join   system_dic  g on g.id=e.property ");
		sql.append(" where a.is_pub=2 ");
		sql.append(" ) jobinfo ");

		sql.append(" where 1=1 ");

		if (isNotEmpty(query.getName())) {
			sql.append(" and jobinfo.name like ? ");
			values.add("%" + query.getName() + "%");
		}
		if (isNotEmpty(query.getCustId())) {
			sql.append(" and jobinfo.custId=? ");
			values.add(query.getCustId());
		}
		if (isNotEmpty(query.getIndustry())) {
			sql.append(" and jobinfo.industry in ( select a.id  from system_dic a where a.category='INDUSTRY' and a.parent_id =" + query.getIndustry() + "  ) ");
		}

		if (isNotEmpty(query.getWorkLocation())) {
			sql.append(" and jobinfo.work_location = ? ");// workLocaiton
			values.add(query.getWorkLocation());
		}
		sql.append(" order by openD desc ");
		System.out.println("TBCGSql:" + sql.toString());

		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());

	}

	/**
	 * 查询待审核的职位
	 */
	public Page findAuditJobPage(ContractJobQuery query) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		// 职位编号 职位所在公司 职位名 职位职责 职位要求 公司简介 职位创建人 职位负责人 审核状态 审核人 审核时间
		// select a.id, c.abbr_name, a.`name`, a.duty, a.requirement,
		// c.job_profile, a.creator, a.job_manager, a.is_pub
		// FROM contract_job a
		// left join customer b on a.cust_id = b.id
		// left join company c on b.company_id = c.id
		// WHERE a.is_pub > 0
		sql.append("select * from (");
		sql.append("select a.id , c.abbr_name abbrName, a.industry,a.name jobName, a.duty, a.requirement, c.job_profile jobProfile,c.property, a.creator, a.job_manager jobManager,a.auditor,a.audit_dt auditDt, a.is_pub isPub ");
		sql.append(" from contract_job a ");
		sql.append(" left join customer b on a.cust_id = b.id  ");
		sql.append(" left join company c on b.company_id = c.id  ");
		sql.append(" where a.is_pub > 0  ");
		sql.append(" ) auditJob ");
		sql.append(" where 1=1 ");

		if (query.getIsPub() != null) {
			sql.append(" and auditJob.isPub = ? ");
			values.add(query.getIsPub());
		}
		if (StringUtils.isNotEmpty(query.getCreator())) {
			sql.append(" and auditJob.creator = ? ");
			values.add(query.getCreator());
		}
		if (StringUtils.isNotEmpty(query.getJobManager())) {
			sql.append(" and auditJob.jobManager = ? ");
			values.add(query.getJobManager());
		}
		if (StringUtils.isNotEmpty(query.getAuditor())) {
			sql.append(" and auditJob.auditor = ? ");
			values.add(query.getAuditor());
		}
		if (StringUtils.isNotEmpty(query.getCustName())) {
			sql.append(" and auditJob.abbrName like ? ");
			values.add("%" + query.getCustName() + "%");
		}
		if (StringUtils.isNotEmpty(query.getName())) {
			sql.append(" and auditJob.jobName like ? ");
			values.add("%" + query.getName() + "%");
		}
		if (isNotEmpty(query.getAuditDtBegin()) && isNotEmpty(query.getAuditDtEnd())) {
			sql.append(" and auditJob.auditDt between  ?  and ?  ");
			values.add(query.getAuditDtBegin());
			values.add(query.getAuditDtEnd());
		} else if (query.getAuditDtBegin() != null && query.getAuditDtEnd() == null) {
			sql.append(" and auditJob.auditDt >= ?   ");
			values.add(query.getAuditDtBegin());
		} else if (isEmpty(query.getAuditDtBegin()) && isNotEmpty(query.getAuditDtEnd())) {
			sql.append(" and auditJob.auditDt <= ?   ");
			values.add(query.getAuditDtEnd());
		}
		if (isNotEmpty(query.getSortColumns())) {
			sql.append(" order by auditJob.").append(query.getSortColumns());
		} else {
			sql.append(" order by auditJob.id desc ");
		}
		return this.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	public Integer passedAudit(String id, String auditor) {
		String sql = "update contract_job a set a.auditor = ? ,a.audit_dt = ? ,a.is_pub = 2   where a.id = ?  ";
		List values = new ArrayList();
		values.add(auditor);
		values.add(new Date());
		values.add(id);
		return this.getJdbcDao().update(sql, values.toArray());
	}

	public Integer rejectAudit(String id, String auditor) {
		String sql = "update contract_job a set a.modifier = ?,a.modified_dt = ?,a.auditor = ? ,a.audit_dt = ? ,a.is_pub = 3   where a.id = ?  ";
		List values = new ArrayList();
		values.add(auditor);
		values.add(new Date());
		values.add(auditor);
		values.add(new Date());
		values.add(id);
		return this.getJdbcDao().update(sql, values.toArray());
	}

	@Override
	public Page findJobInProjectPage(ContractJobQuery query) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from (");
		sql.append(" select a.id projectId ,a.name projectName,a.account,b.id,b.job_id jobId ");
		sql.append(" from search_project a ");
		sql.append(" inner join contract_job_search_project b  on a.id=b.project_id  ");
		sql.append(" ) pj ");
		sql.append(" where 1=1 ");

		if (isNotEmpty(query.getJobId())) {
			sql.append(" and pj.jobId = ? ");
			values.add(query.getJobId());
		}
		return this.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	@Override
	public boolean hasOfferBill(Integer jobId) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		if (jobId != null && jobId > 0) {
			sql.append(" select * from (");
			sql.append(" select a.id offerId,b.job_id jobId  ");
			sql.append(" from contract_job_cv_offer a ");
			sql.append(" inner join contract_job_cv_recom b  on b.id=a.recom_id   ");
			sql.append(" inner join account_pn c  on c.offer_id = a.id   ");
			sql.append(" ) t1 ");
			sql.append(" where 1=1 ");
			sql.append(" and t1.jobId = ? ");
			values.add(jobId);
			List list = this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
			if (list != null && list.size() > 0) {
				return true;
			}
		}
		return false;
	}

}
